﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;

namespace blackhouse.Data {
    public abstract class AdoDataHelper {

        #region Fields

        private List<DbParameter> parameters = new List<DbParameter>();

        private string command;
        private CommandType commandType = CommandType.StoredProcedure;

        #endregion

        #region Properties

        public string ProcedureName {
            get { return this.command; }
            set {
                this.command = value;
                this.commandType = CommandType.StoredProcedure;
            }
        }

        public string Query {
            get { return this.command; }
            set {
                this.command = value;
                this.commandType = CommandType.Text;
            }
        }

        #endregion

        #region Methods

        protected abstract DbConnection CreateConnection();

        protected abstract DbParameter CreateParameter();

        protected abstract DbDataAdapter CreateDataAdapter();

        public DbParameter AddParameter(string parameterName, object value) {
            return this.AddParameter(parameterName, value, null, DbType.String, 0);
        }

        public DbParameter AddParameter(string parameterName, object value, DbType type) {
            return this.AddParameter(parameterName, value, null, type, 0);
        }

        public DbParameter AddParameter(string parameterName, object value, object fallbackValue) {
            return this.AddParameter(parameterName, value, fallbackValue, DbType.String, 0);
        }

        public DbParameter AddParameter(string parameterName, object value, object fallbackValue, DbType type) {
            return this.AddParameter(parameterName, value, fallbackValue, type, 0);
        }

        public DbParameter AddParameter(string parameterName, object value, DbType type, int size) {
            return this.AddParameter(parameterName, value, null, type, size);
        }

        public DbParameter AddParameter(string parameterName, object value, object fallbackValue, DbType type, int size) {
            DbParameter parameter = this.CreateParameter();

            if (parameterName.StartsWith("@"))
                parameterName = "@" + parameterName;
            parameter.ParameterName = parameterName;
            parameter.Value = value;

            if (parameter.Value == null) {
                if (fallbackValue != null)
                    parameter.Value = fallbackValue;
                else
                    parameter.Value = System.DBNull.Value;
            }
            parameter.DbType = type;
            if (size > 0) {
                if (size < value.ToString().Length)
                    return null;
                parameter.Size = size;
            }
            this.parameters.Add(parameter);
            return parameter;
        }

        public IDataReader Execute() {
            if (string.IsNullOrEmpty(ProcedureName))
                throw new InvalidOperationException("Procedure name isn't set");

            IDataReader reader = null;
            DbConnection connection = this.CreateConnection();
            connection.Open();
            DbCommand command = connection.CreateCommand();
            this.SetCommand(command);
            reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;

        }

        public object ExecuteProcedureResult() {
            DbParameter returnPrm = this.CreateParameter();
            returnPrm.Direction = ParameterDirection.ReturnValue;
            this.parameters.Add(returnPrm);
            this.ExecuteWithoutResult();
            return returnPrm.Value;
        }

        public T[] Execute<T>(AdoRead<T> read) {
            if (string.IsNullOrEmpty(ProcedureName))
                throw new InvalidOperationException("Procedure name isn't set");

            IDataReader reader = null;
            List<T> objects = new List<T>();
            using (DbConnection connection = this.CreateConnection()) {
                connection.Open();
                DbCommand command = connection.CreateCommand();
                this.SetCommand(command);
                reader = command.ExecuteReader();
                while (reader.Read()) {
                    objects.Add(read(reader));
                }
                connection.Close();
            }
            return objects.ToArray();
        }

        public DataTable ExecuteDataTable() {
            DataTable dt = new DataTable();
            using (DbConnection connection = this.CreateConnection()) {
                connection.Open();
                DbDataAdapter da = this.CreateDataAdapter();
                DbCommand command = connection.CreateCommand();
                this.SetCommand(command);
                da.SelectCommand = command;
                da.Fill(dt);
                connection.Close();
            }
            return dt;
        }

        public DataTable ExecuteScheme() {
            DataTable dt = new DataTable();
            using (DbConnection connection = this.CreateConnection()) {
                connection.Open();
                DbDataAdapter da = this.CreateDataAdapter();
                DbCommand command = connection.CreateCommand();
                this.SetCommand(command);
                da.SelectCommand = command;
                da.FillSchema(dt, SchemaType.Source);
                connection.Close();
            }
            return dt;
        }

        public object ExecuteScalar() {
            if (string.IsNullOrEmpty(ProcedureName))
                throw new InvalidOperationException("Procedure name isn't set");

            object obj = null;
            using (DbConnection connection = this.CreateConnection()) {
                connection.Open();
                DbCommand command = connection.CreateCommand();
                this.SetCommand(command);
                obj = command.ExecuteScalar();
                connection.Close();
            }
            return obj;
        }

        public T ExecuteScalar<T>() {
            if (string.IsNullOrEmpty(ProcedureName))
                throw new InvalidOperationException("Procedure name isn't set");

            T obj = default(T);
            using (DbConnection connection = this.CreateConnection()) {
                connection.Open();
                DbCommand command = connection.CreateCommand();
                this.SetCommand(command);
                obj = (T)command.ExecuteScalar();
                connection.Close();
            }
            return obj;
        }

        public int ExecuteWithoutResult() {
            if (string.IsNullOrEmpty(ProcedureName))
                throw new InvalidOperationException("Procedure name isn't set");

            int affected = 0;
            using (DbConnection connection = this.CreateConnection()) {
                connection.Open();
                DbCommand command = connection.CreateCommand();
                this.SetCommand(command);
                affected = command.ExecuteNonQuery();
                connection.Close();
            }
            return affected;
        }

        public void Reset() {
            this.ProcedureName = "";
            this.parameters.Clear();
        }

        public void SetTransaction() {

        }

        private void SetCommand(DbCommand command) {
            command.CommandType = this.commandType;
            command.CommandText = this.command;
            command.CommandTimeout = 0;
            if (this.parameters.Count > 0)
                command.Parameters.AddRange(this.parameters.ToArray());
        }

        #endregion

    }
}
